Working with images with Perl in MySQL

In this chapter of the MySQL Perl tutorial, we will work with image files. Note that some people argue against putting images into databases. Here we only show how to do it. We do not dwell into technical issues of whether to save images in databases or not.

mysql> CREATE TABLE Images(Id INT PRIMARY KEY, Data MEDIUMBLOB);

For this example, we create a new table called Images. For the images, we use the MySQL MEDIUMBLOB data type, which stores binary objects.

Inserting images

In the first example, we are going to insert an image to the MySQL database.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

open IMAGE, "woman.jpg" or die $!;

my ($image, $buff);
while(read IMAGE, $buff, 1024) {
    $image .= $buff;
}

my $stm = $dbh->prepare("INSERT INTO Images(Id, Data) VALUES (1, ?)");
$stm->bind_param(1, $image, DBI::SQL_BLOB);
$stm->execute();

close(IMAGE);
$stm->finish();
$dbh->disconnect();

We read an image from the current working directory and write it into the Images table of the MySQL mydb database.

open IMAGE, "woman.jpg" or die $!;

We open an image. It is a JPG image called woman.jpg.

my ($image, $buff);
while(read IMAGE, $buff, 1024) {
    $image .= $buff;
}

We read binary data from the image file.

my $stm = $dbh->prepare("INSERT INTO Images(Id, Data) VALUES (1, ?)");
$stm->bind_param(1, $image, DBI::SQL_BLOB);
$stm->execute();

The three code lines prepare the SQL statement, bind the image data to the statement and execute it.

close(IMAGE);
$sth->finish();
$dbh->disconnect();

Finally, we are releasing the resources.

Reading images

In this section, we are going to perform the reverse operation. We will read an image from the database table.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $stm = $dbh->prepare("SELECT Data FROM Images WHERE Id=1");
$stm->execute();
my $image = $stm->fetch();

open IMAGE, ">woman2.jpg" or die $!;
print IMAGE @$image;
close(IMAGE);

$stm->finish();
$dbh->disconnect();

We read image data from the Images table and write it to another file, which we call woman2.jpg.

my $sth = $dbh->prepare("SELECT Data FROM Images WHERE Id=1");
$sth->execute();
my $image = $sth->fetch();

These three lines select the image data from the table.

open IMAGE, ">woman2.jpg" or die $!;
print IMAGE @$image;
close(IMAGE);

We open a new image file and write the retrieved data into that file. Then we close the file.

This part of the MySQL Perl tutorial was dedicated to reading and writing images.